package entity;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class jdba {
    //连接数据库
    private static Connection getConn() {
        String url = "jdbc:mysql://localhost:3306/t?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC";
        String user = "root";
        String password = "whn1314";
        Connection conn = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }


    public static ResultSet execute2(String sql, String... args) {

        Connection conn = getConn();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, args[0]);
            return ps.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return null;
    }
    //增加书本
    public static int Addbook(Book book) {
        Connection conn = getConn();
        int i = 0;
        String sql = "insert into book (id,name,author,price) values(?,?,?,?)";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setInt(1,book.getId());
            pstmt.setString(2, book.getName());
            pstmt.setString(3,book.getAuthor());
            pstmt.setFloat(4, book.getPrice());
            i= pstmt.executeUpdate();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }
    //删除
    public static  int delete(int ID) {
        Connection conn = getConn();
        int i = 0;
        String sql = "delete from book where id='" + ID + "'";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
            System.out.println("result: " + i);
            pstmt.close();
            conn.close();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }
    //更新学生信息
    public static int update(Book  book ){
        System.out.println("----修改");
        System.out.println("000000"+book.getId());
        Connection conn=getConn();
        int id=book.getId();
        int i=0;
        String sql="update book set name=?,sex=?,age=?,hobby=? where ID='" + id +"'";
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,book.getId());
            pstmt.setString(2, book.getName());
            pstmt.setString(3,book.getAuthor());
            pstmt.setFloat(4, book.getPrice());
            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }



        return i;
    }

    //返回全部信息
    public static List<Book> getALLInfo(){
        ArrayList<Book> list=new ArrayList<>();
        Connection conn=getConn();
        //做查询
        String sql="select * from book";
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            //获取结果集
            ResultSet rs=ps.executeQuery();

            while(rs.next()){
                Book book=new Book();   //new一个对象代表一条记录
                //吧数据库里面的信息取出来放到实体类里面
                book.setId(Integer.parseInt(rs.getString("id")));
                book.setName((rs.getString("name")));
                book.setAuthor((rs.getString("author")));

                book.setPrice((rs.getFloat("price")));

                //把取出的数据添加到list集合里面
                list.add(book);

            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;

    }
}
